﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Transactions;

namespace DataAccessLayer
{
    public class StoreRetrievalDAL
    {
        StationeryInventoryEntities1 cntx = new StationeryInventoryEntities1();

        public IEnumerable<Object> getStoreRetrievalList()
        {
            var result = (from dr in cntx.DeptRequisitions
                          join drd in cntx.DeptRequisitionDetails
                          on dr.DeptReqID equals drd.DeptReqID
                          join s in cntx.Stationeries
                          on drd.ItemCode equals s.ItemCode
                          where dr.RetrievedDate == null
                          group drd by new { drd.ItemCode, s.BinNo, s.ItemName, s.AvailableQty } into g
                          select new
                          {
                              BinNo = g.Key.BinNo,
                              ItemCode = g.Key.ItemCode,
                              ItemName = g.Key.ItemName,
                              RequestQuantity = (System.Int32?)g.Sum(drd => drd.NewRequestQty),
                              AvailableQty = g.Key.AvailableQty,
                              ActualQty = (System.Int32?)g.Sum(drd => drd.ActualQty)
                          }).ToList();
            return result;

        }

        public void AutoAllocateActualQty()
        {
            List<DeptRequisitionDetail> dpr = (from dp in cntx.DeptRequisitionDetails
                                               where dp.ActualQty == null
                                               select dp).ToList();
            foreach (DeptRequisitionDetail d in dpr)
            {
                string itm = d.ItemCode;
                Stationery stn = (from s in cntx.Stationeries
                                  where s.ItemCode == itm
                                  select s).FirstOrDefault();
                if (d.NewRequestQty <= stn.AvailableQty)
                {
                    d.ActualQty = d.NewRequestQty;

                }
                else
                {
                    d.ActualQty = stn.AvailableQty;
                }
            }
            cntx.SaveChanges();
        }

        public void ConfirmWarehouseCollection(String empId)
        {
            List<DeptRequisition> DeptReqIds = (from did in cntx.DeptRequisitions
                                                where did.RetrievedDate == null
                                                select did).ToList();
            foreach (DeptRequisition d in DeptReqIds)
            {
                int did = d.DeptReqID;
                List<DeptRequisitionDetail> DeptReqDetails = (from drd in cntx.DeptRequisitionDetails
                                                              where drd.DeptReqID == did
                                                              && drd.ActualQty != null
                                                              select drd).ToList();

                Department dept = cntx.Departments.FirstOrDefault(x => x.DeptID == d.DeptID);

                CollectionPoint cp = cntx.CollectionPoints.FirstOrDefault(x => x.CollectionPointID == dept.CollectionPointID);
                DateTime daysUntilMonday = DateTime.Today.AddDays(((int)DateTime.Today.DayOfWeek - (int)DayOfWeek.Monday)+1);

                CollectionPointDetail cpd = new CollectionPointDetail();
                cpd.CollectionPointID = Convert.ToInt32(d.Department.CollectionPointID);
                cpd.CollectionTime = Convert.ToDateTime(cp.CollectionTime);
                cpd.CollectionDate = daysUntilMonday;

                cntx.CollectionPointDetails.Add(cpd);
                cntx.SaveChanges();


                foreach (DeptRequisitionDetail drs in DeptReqDetails)
                {
                    int did1 = (int)drs.DeptReqID;

                    if (drs.NewRequestQty > drs.ActualQty)
                    {
                        OutstandingQty o = new OutstandingQty();
                        o.DeptID = d.DeptID;
                        o.ItemCode = drs.ItemCode;
                        o.OutstandingQty1 = drs.NewRequestQty - drs.ActualQty;
                        o.Status = "Outstanding";
                        cntx.OutstandingQties.Add(o);
                        cntx.SaveChanges();
                    }
                    DeptRequisition dr = (from d1 in cntx.DeptRequisitions
                                          where d1.DeptReqID == drs.DeptReqID
                                          select d1).FirstOrDefault();


                    CollectionPointDetail c = (from cpd1 in cntx.CollectionPointDetails
                                               where cpd1.CollectionPointID == dept.CollectionPointID
                                               orderby cpd1.CollectionPointDetailID descending
                                               select cpd1).FirstOrDefault();

                    dr.RetrievedDate = DateTime.Now;
                    dr.EndDate = DateTime.Today.AddDays(-1);
                    dr.RetrievedBy = Convert.ToInt32(empId);
                    dr.CollectionPointDetailID = c.CollectionPointDetailID;
                    cntx.SaveChanges();
                }
            }
            cntx.SaveChanges();
        }

        public IEnumerable<Object> getItemWiseDeptRequests(String iCode)
        {
            var query = (from dr in cntx.DeptRequisitions
                         join drd in cntx.DeptRequisitionDetails
                         on dr.DeptReqID equals drd.DeptReqID
                         join s in cntx.Stationeries
                         on drd.ItemCode equals s.ItemCode
                         join d in cntx.Departments
                         on dr.DeptID equals d.DeptID
                         where drd.ItemCode == iCode && dr.RetrievedDate == null
                         select new { drd.DeptReqDetailID, d.DeptName, drd.NewRequestQty, s.AvailableQty, drd.ActualQty }).ToList();
            return query;
        }

        public void allocateActualQty(int DepReqID, int ActualQty)
        {
            var drd = (from d in cntx.DeptRequisitionDetails
                       where d.DeptReqDetailID == DepReqID
                       select d).First();

            drd.ActualQty = ActualQty;
            cntx.SaveChanges();
        }

        public void updateDeptReqDetActualQty(List<int> lstDeptReqDetId, List<int> lstAllocatedQty)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                for (int i = 0; i < lstDeptReqDetId.Count; i++)
                {
                    int num = lstDeptReqDetId[i];
                    DeptRequisitionDetail deptDet = cntx.DeptRequisitionDetails.FirstOrDefault(x => x.DeptReqDetailID == num);

                    deptDet.ActualQty = lstAllocatedQty[i];
                    cntx.SaveChanges();
                }
                ts.Complete();
            }

        }

       
    }
}